CREATE DATABASE TaskManager
GO

USE TaskManager
GO

CREATE TABLE Users
(
	Id			INT PRIMARY KEY IDENTITY(1,1),
	UserName	VARCHAR(100),
	Password	VARCHAR(100),
	FirstName	NVARCHAR(100),
	LastName	NVARCHAR(100),
	Address		NVARCHAR(500),
	DateOfBirth DATE,
	Gender		BIT,
	DepartmentId	INT,
	Email		nvarchar(50),
	Mission		nvarchar(100),
	Avatar		NVARCHAR(200),
	IsActive	BIT,
	IsAdmin		BIT,
	IsManager	BIT,
	CreateDate	DATETIME,
	ModifyDate	DATETIME,
	CreateBy	INT,
	ModifyBy	INT
)
GO

CREATE TABLE Departments
(
	Id			INT PRIMARY KEY IDENTITY(1,1),
	Name		NVARCHAR(100),
	UserId		INT,
	CreateDate	DATETIME,
	ModifyDate	DATETIME,
	CreateBy	INT,
	ModifyBy	INT
)
GO

CREATE TABLE Projects
(
	Id			INT PRIMARY KEY IDENTITY(1,1),
	Name		NVARCHAR(200),
	Description NVARCHAR(500),
	StartDate	DATETIME,
	EndDate		DATETIME,
	CreateDate	DATETIME,
	ModifyDate	DATETIME,
	CreateBy	INT,
	ModifyBy	INT
)
GO

CREATE TABLE Tasks
(
	Id			INT PRIMARY KEY IDENTITY(1,1),
	ProjectId	INT,
	Name		NVARCHAR(200),
	Leader		INT,
	Priority	INT,
	Description NVARCHAR(500),
	StartDate	DATETIME,
	EndDate		DATETIME,
	CreateDate	DATETIME,
	ModifyDate	DATETIME,
	CreateBy	INT,
	ModifyBy	INT
)
GO

CREATE TABLE TaskDocs
(
	TaskId				INT,
	DocumentId			INT,
)
GO

CREATE TABLE AssignTasks
(
	Id					INT PRIMARY KEY IDENTITY(1,1),
	TaskId				INT,
	UserId				INT,
	Requirement			NVARCHAR(500),
	CompletedPercent	INT,
	StartDate			DATETIME,
	EndDate				DATETIME,
	CreateDate			DATETIME,
	ModifyDate			DATETIME,
	CreateBy			INT,
	ModifyBy			INT
)



CREATE TABLE Documents
(
	Id					INT PRIMARY KEY IDENTITY(1,1),
	Name				NVARCHAR(200),
	FilePath			NVARCHAR(500)
)

CREATE TABLE Reports
(
	Id					INT PRIMARY KEY IDENTITY(1,1),
	AssignTaskId		INT,
	TaskId				INT,
	ReportDate			DATETIME,
	ReportResult		NVARCHAR(500),
	NextTask			NVARCHAR(500),
	CompletedPercent	INT,
	UserReport			INT,
	Comment				NVARCHAR(500),
	CommentBy			INT,
	CommentDate			DATETIME,
	ReportType			INT,
	CreateDate			DATETIME,
	ModifyDate			DATETIME,
	CreateBy			INT,
	ModifyBy			INT
)

CREATE TABLE ReportDocs
(
	ReportId			INT,
	DocumentId			INT
)
GO

CREATE TABLE Appointments
(
	Id					INT PRIMARY KEY IDENTITY(1,1),
	Title				NVARCHAR(200),
	Description			NVARCHAR(500),
	StartDate			DATETIME,
	EndDate				DATETIME,
	CreateDate			DATETIME,
	ModifyDate			DATETIME,
	CreateBy			INT,
	ModifyBy			INT
)
GO

CREATE TABLE Attendees
(
	AppointmentId		INT,
	UserId				INT
)
GO

CREATE TABLE ChatRooms
(
	Id					INT PRIMARY KEY IDENTITY(1,1),
	FromUserId			INT,
	ToUserId			INT,
	CreateDate			DATETIME
)
GO

CREATE TABLE ChatMessages
(
	Id					BIGINT PRIMARY KEY IDENTITY(1,1),
	RoomId				INT,
	Message				NVARCHAR(1000),
	FromUserId			INT,
	ToUserId			INT,
	CreateDate			DATETIME,
	CreateDateStamp		BIGINT,
	IsRead				BIT,
	Status				INT
)
GO

CREATE TABLE Alerts
(
	Id					BIGINT PRIMARY KEY IDENTITY(1,1),
	Message				NVARCHAR(1000),
	UserId				INT,
	ReferenceId			INT,
	TypeId				INT,
	IsRead				BIT
)
GO

CREATE TABLE AppointmentComments
(
	Id					BIGINT PRIMARY KEY IDENTITY(1,1),
	CommentType			INT,
	AppointmentId		INT,
	Comments			NVARCHAR(1000),
	CreateDate			DATETIME,
	ModifyDate			DATETIME,
	CreateBy			INT,
	ModifyBy			INT
)


INSERT INTO Departments VALUES ('Admin',1, null, null, null, null)
INSERT INTO Users VALUES ('admin','dSVrtpZkGVI=', 'admin','', N'Thái Nguyên', '08/30/1990', 0, 1,'nguyenthimet@gmail.com','abc', '/Content/img/avatar3.png',1,1,0, null, null, null, null)
